﻿

if(exists(select 1 from sys.all_objects where name='T_ProgressRecord'))
begin
	drop table [T_ProgressRecord]
end
go

--创建表
CREATE TABLE [dbo].[T_ProgressRecord]
(
	[ProgressRecordId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY, 
	[CustomerId] UNIQUEIDENTIFIER NOT NULL,
    [RecordTypeId] INT NULL, 
    [ProgressTypeId] INT NULL, 
    [RecordTime] VARCHAR(20) NULL, 
    [RecordContent] VARCHAR(8000) NULL,
	[CreateTime]         VARCHAR (20)     NULL,
    [CreateUserId]       INT              NULL,
    [UpdateTime]         VARCHAR (20)     NULL,
    [UpdateUserId]       INT              NULL,
    [Valid]              INT              NULL, 
)
go

if(exists(select 1 from sys.all_objects where name='View_T_ProgressRecord'))
begin
	drop view [View_T_ProgressRecord]
end

go
--创建视图
CREATE VIEW [dbo].[View_T_ProgressRecord]
AS 
SELECT 
T_ProgressRecord.* 
,CustomerName=Sys_User.UserName
,RecordTypeName=RT.ClassificationName
,ProgressTypeName=PT.ClassificationName
FROM T_ProgressRecord
left outer join Sys_User on Sys_User.UserId=T_ProgressRecord.CreateUserId
left outer join T_Classification as RT on RT.ClassificationId=T_ProgressRecord.RecordTypeId
left outer join T_Classification as PT on PT.ClassificationId=T_ProgressRecord.ProgressTypeId

go